<?php

static $config = NULL;
static $log = NULL;

// Error Handler
function error_handler_for_export($errno, $errstr, $errfile, $errline) {
	global $config;
	global $log;
	
	switch ($errno) {
		case E_NOTICE:
		case E_USER_NOTICE:
			$errors = "Notice";
			break;
		case E_WARNING:
		case E_USER_WARNING:
			$errors = "Warning";
			break;
		case E_ERROR:
		case E_USER_ERROR:
			$errors = "Fatal Error";
			break;
		default:
			$errors = "Unknown";
			break;
	}
		
	if (($errors=='Warning') || ($errors=='Unknown')) {
		return true;
	}

	

	return true;
}


class Model_Export extends CI_Model {


	function clean( &$str, $allowBlanks=FALSE ) {
		$result = "";
		$n = strlen( $str );
		for ($m=0; $m<$n; $m++) {
			$ch = substr( $str, $m, 1 );
			if (($ch==" ") && (!$allowBlanks) || ($ch=="\n") || ($ch=="\r") || ($ch=="\t") || ($ch=="\0") || ($ch=="\x0B")) {
				continue;
			}
			$result .= $ch;
		}
		return $result;
	}


	function import( &$database, $sql ) {
		foreach (explode(";\n", $sql) as $sql) {
			$sql = trim($sql);
			if ($sql) {
				$database->query($sql);
			}
		}
	}


	protected function getDefaultLanguageId( &$database ) {
		//$code = $this->config->get('config_language');
		$this->load->model('setting/Model_Config');
		$code = $this->Model_Config->getLanguageID($this->Model_Config->getKey('config_admin_language'));
		$sql = "SELECT language_id FROM `language` WHERE language_id = '$code'";
		$result = $database->query( $sql );
		$languageId = 6;
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$languageId = $row['language_id'];
				break;
			}
		}
		return $languageId;
	}


	protected function getDefaultWeightUnit() {
		/*$weightUnit = $this->config->get( 'config_weight_class' );
		return $weightUnit;*/
	}


	protected function getDefaultMeasurementUnit() {
		/*$measurementUnit = $this->config->get( 'config_length_class' );
		return $measurementUnit;*/
	}



	function storeManufacturersIntoDatabase( &$database, &$products, &$manufacturerIds ) {
		// find all manufacturers already stored in the database
		$sql = "SELECT `manufacturer_id`, `name` FROM `manufacturer`;";
		$result = $database->query( $sql );
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$manufacturerId = $row['manufacturer_id'];
				$name = $row['name'];
				if (!isset($manufacturerIds[$name])) {
					$manufacturerIds[$name] = $manufacturerId;
				}
			}
		}

		// add newly introduced manufacturers to the database
		$maxManufacturerId=0;
		foreach ($manufacturerIds as $manufacturerId) {
			$maxManufacturerId = max( $maxManufacturerId, $manufacturerId );
		}
		$sql = "INSERT INTO `manufacturer` (`manufacturer_id`, `name`, `image`, `sort_order`) VALUES "; 
		$k = strlen( $sql );
		$first = TRUE;
		foreach ($products as $product) {
			$manufacturerName = $product[6];
			if ($manufacturerName=="") {
				continue;
			}
			if (!isset($manufacturerIds[$manufacturerName])) {
				$maxManufacturerId += 1;
				$manufacturerId = $maxManufacturerId;
				$manufacturerIds[$manufacturerName] = $manufacturerId;
				$sql .= ($first) ? "\n" : ",\n";
				$first = FALSE;
				$sql .= "($manufacturerId, '$manufacturerName', '', 0)";
			}
		}
		$sql .= ";\n";
		if (strlen( $sql ) > $k+2) {
			$database->query( $sql );
		}
		
		// populate manufacturer_to_store table
		$storeIdsForManufacturers = array();
		foreach ($products as $product) {
			$manufacturerName = $product[6];
			if ($manufacturerName=="") {
				continue;
			}
			$manufacturerId = $manufacturerIds[$manufacturerName];
			$storeIds = $product[33];
			if (!isset($storeIdsForManufacturers[$manufacturerId])) {
				$storeIdsForManufacturers[$manufacturerId] = array();
			}
			foreach ($storeIds as $storeId) {
				if (!in_array($storeId,$storeIdsForManufacturers[$manufacturerId])) {
					$storeIdsForManufacturers[$manufacturerId][] = $storeId;
					$sql2 = "INSERT INTO `manufacturer_to_store` (`manufacturer_id`,`store_id`) VALUES ($manufacturerId,$storeId);";
					$database->query( $sql2 );
				}
			}
		}
		return TRUE;
	}


	function getWeightClassIds( &$database ) {
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// find all weight classes already stored in the database
		$weightClassIds = array();
		$sql = "SELECT `weight_class_id`, `unit` FROM `weight_class_description` WHERE `language_id`=$languageId;";
		$result = $database->query( $sql );
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$weightClassId = $row['weight_class_id'];
				$unit = $row['unit'];
				if (!isset($weightClassIds[$unit])) {
					$weightClassIds[$unit] = $weightClassId;
				}
			}
		}

		return $weightClassIds;
	}


	function getLengthClassIds( &$database ) {
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// find all length classes already stored in the database
		$lengthClassIds = array();
		$sql = "SELECT `length_class_id`, `unit` FROM `length_class_description` WHERE `language_id`=$languageId;";
		$result = $database->query( $sql );
		if ($result->rows) {
			foreach ($result->rows as $row) {
				$lengthClassId = $row['length_class_id'];
				$unit = $row['unit'];
				if (!isset($lengthClassIds[$unit])) {
					$lengthClassIds[$unit] = $lengthClassId;
				}
			}
		}

		return $lengthClassIds;
	}


	function storeProductsIntoDatabase( &$database, &$products ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove products
		$sql = "START TRANSACTION;\n";
		//$sql .= "DELETE FROM `product`;\n";
		//$sql .= "DELETE FROM `product_description` WHERE language_id=$languageId;\n";
		//$sql .= "DELETE FROM `product_to_category`;\n";
		//$sql .= "DELETE FROM `product_attribute`;\n";
		//$sql .= "DELETE FROM `product_to_store`;\n";
		//$sql .= "DELETE FROM `manufacturer_to_store`;\n";
		//$sql .= "DELETE FROM `url_alias` WHERE `query` LIKE 'product_id=%';\n";
		//$sql .= "DELETE FROM `product_related`;\n";
		//$sql .= "DELETE FROM `product_image`;\n";
		//$sql .= "DELETE FROM `product_tags` WHERE language_id=$languageId;\n";
		$this->import( $database, $sql );
		
		// store or update manufacturers
		/*$manufacturerIds = array();
		$ok = $this->storeManufacturersIntoDatabase( $database, $products, $manufacturerIds );
		if (!$ok) {
			$database->query( 'ROLLBACK;' );
			return FALSE;
		}*/
		
		// get weight classes
		//$weightClassIds = $this->getWeightClassIds( $database );
		
		// get length classes
		//$lengthClassIds = $this->getLengthClassIds( $database );
		
		// generate and execute SQL for storing the products
		foreach ($products as $product) {
			$productId = $product[0];
			$productName = addslashes($product[1]);
			$packet = $product[2];
			$lab = $product[3];
			$model = $product[4];
			$share = $product[5];
			$weight = $product[6];
			$colour = $product[7];
			$clarity = $product[8];
			$cut = $product[9];
			$polish = $product[10];
			$sym = $product[11];
			$flour = $product[12];
			$price = $product[13];
			$avgdia = $product[14];
			$mindia = $product[15];
			$maxdia = $product[16];
			$depth = $product[17];
			$table = $product[18];
			$dmm = $product[19];
			$tint = $product[20];
			$image = $product[21];
			$date_added = $product[22];
			$date_modified = $product[23];
					
		$this->db->trans_start();			
		
		$basic_data = array(
			"image"	=> $image,
			"quantity"	=> '1',
			"model"	=> $model,
			"weight"	=> $weight,
			"packet"	=> $packet,
			"price"	=> (float)$price,
			"status" => '1',
			"date_added"	=> date("Y-m-d H:i:s"),
		);
		
		$this->db->insert('product', $basic_data);
		
		$product_id = $this->db->insert_id();
		
		$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"name"			=> $productName,
		);
		$this->db->insert('product_description', $basic_data);
		
		$basic_data = array(
			"product_id"		=> (int)$product_id,
			"customer_group_id"		=> 12,
			"points"		=> $price/100,
		);
					
		$this->db->insert('product_reward', $basic_data);
			
		$basic_data = array(
			"product_id"		=> (int)$product_id,
			"customer_group_id"		=> 8,
			"points"		=> $price/100,
		);
					
		$this->db->insert('product_reward', $basic_data);
					
		$basic_data = array(
			"product_id"		=> (int)$product_id,
			"customer_group_id"		=> 13,
			"points"		=> $price/100,
		);
		
		$this->db->insert('product_reward', $basic_data);
		
		$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"name"			=> $productName,
		);
		$this->db->insert('product_description', $basic_data);
		
		$basic_data = array(
			"product_id"	=> (int)$product_id,
			"store_id"	=> '0',
		);
		$this->db->insert('product_to_store', $basic_data);
		
		/*for($i=1;$i<=7;$i++){
			$basic_data = array(
				"product_id"	=> (int)$product_id,
				"category_id"	=> (int)$i,
			);
			$this->db->insert('product_to_category', $basic_data);
		}*/
		
		$basic_data = array(
				"product_id"	=> (int)$product_id,
				"category_id"	=> '1',
			);
			$this->db->insert('product_to_category', $basic_data);
		
		if($lab!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '4',
			"text"			=> $lab,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($share!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '5',
			"text"			=> $share,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
	/*	if($carats!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '6',
			"text"			=> $carats,
		);
		$this->db->insert('product_attribute', $basic_data);
		}*/
		
		if($colour!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '7',
			"text"			=> $colour,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($clarity!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '8',
			"text"			=> $clarity,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($cut!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '3',
			"text"			=> $cut,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($polish!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '9',
			"text"			=> $polish,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($sym!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '10',
			"text"			=> $sym,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($flour!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '11',
			"text"			=> $flour,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($avgdia!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '12',
			"text"			=> $avgdia,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($mindia!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '13',
			"text"			=> $mindia,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($maxdia!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '14',
			"text"			=> $maxdia,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($depth!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '15',
			"text"			=> $depth,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($table!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '16',
			"text"			=> $table,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($dmm!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '17',
			"text"			=> $dmm,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($tint!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> (int)$languageId,
			"attribute_id"	=> '18',
			"text"			=> $tint,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($lab!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '4',
			"text"			=> $lab,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($share!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '5',
			"text"			=> $share,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($carats!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '6',
			"text"			=> $carats,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($colour!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '7',
			"text"			=> $colour,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($clarity!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '8',
			"text"			=> $clarity,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($cut!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '3',
			"text"			=> $cut,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($polish!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '9',
			"text"			=> $polish,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($sym!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '10',
			"text"			=> $sym,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($flour!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '11',
			"text"			=> $flour,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($avgdia!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '12',
			"text"			=> $avgdia,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($mindia!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '13',
			"text"			=> $mindia,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($maxdia!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '14',
			"text"			=> $maxdia,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($depth!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '15',
			"text"			=> $depth,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($table!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '16',
			"text"			=> $table,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($dmm!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '17',
			"text"			=> $dmm,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		if($tint!=''){
			$basic_data = array(
			"product_id"	=> (int)$product_id,
			"language_id"	=> 3,
			"attribute_id"	=> '18',
			"text"			=> $tint,
		);
		$this->db->insert('product_attribute', $basic_data);
		}
		
		
		/*$this->db->insert('product', $basic_data);
			$sql2 = "INSERT INTO `product_description` (`product_id`,`language_id`,`name`) VALUES ($productId,$languageId,'$productName');";
			
			$sql3 = "INSERT INTO `product_image` (`product_id`,`image`) VALUES ";
			$sql3 .= "($productId,'$additionalimage');";
			
			$database->query($sql);
			$database->query($sql2);*/
			//$database->query($sql3);
			
		/*	if (count($categories) > 0) {
				$sql = "INSERT INTO `product_to_category` (`product_id`,`category_id`) VALUES ";
				$first = TRUE;
				foreach ($categories as $categoryId) {
					$sql .= ($first) ? "\n" : ",\n";
					$first = FALSE;
					$sql .= "($productId,$categoryId)";
				}
				$sql .= ";";
				$database->query($sql);
			}
			if ($keyword) {
				$sql4 = "INSERT INTO `url_alias` (`query`,`keyword`) VALUES ('product_id=$productId','$keyword');";
				$database->query($sql4);
			}
			foreach ($storeIds as $storeId) {
				$sql6 = "INSERT INTO `product_to_store` (`product_id`,`store_id`) VALUES ($productId,$storeId);";
				$database->query($sql6);
			}
			if (count($related) > 0) {
				$sql = "INSERT INTO `product_related` (`product_id`,`related_id`) VALUES ";
				$first = TRUE;
				foreach ($related as $relatedId) {
					$sql .= ($first) ? "\n" : ",\n";
					$first = FALSE;
					$sql .= "($productId,$relatedId)";
				}
				$sql .= ";";
				$database->query($sql);
			}
			if (count($tags) > 0) {
				$sql = "INSERT INTO `product_tags` (`product_id`,`tag`,`language_id`) VALUES ";
				$first = TRUE;
				$inserted_tags = array();
				foreach ($tags as $tag) {
					if ($tag == '') {
						continue;
					}
					if (in_array($tag,$inserted_tags)) {
						continue;
					}
					$sql .= ($first) ? "\n" : ",\n";
					$first = FALSE;
					$sql .= "($productId,'".addslashes($tag)."',$languageId)";
					$inserted_tags[] = $tag;
				}
				$sql .= ";";
				if (count($inserted_tags)>0) {
					$database->query($sql);
				}
			}*/
		}
		
		// final commit
		$database->query("COMMIT;");
		return TRUE;
	}
	
	function storeProductsSetDatabase( &$database, &$products ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove products
		$sql = "START TRANSACTION;\n";
		//$sql .= "DELETE FROM `product`;\n";
		//$sql .= "DELETE FROM `product_description` WHERE language_id=$languageId;\n";
		//$sql .= "DELETE FROM `product_to_category`;\n";
		//$sql .= "DELETE FROM `product_attribute`;\n";
		//$sql .= "DELETE FROM `product_to_store`;\n";
		//$sql .= "DELETE FROM `manufacturer_to_store`;\n";
		//$sql .= "DELETE FROM `url_alias` WHERE `query` LIKE 'product_id=%';\n";
		//$sql .= "DELETE FROM `product_related`;\n";
		//$sql .= "DELETE FROM `product_image`;\n";
		//$sql .= "DELETE FROM `product_tags` WHERE language_id=$languageId;\n";
		$this->import( $database, $sql );
		
		// store or update manufacturers
		/*$manufacturerIds = array();
		$ok = $this->storeManufacturersIntoDatabase( $database, $products, $manufacturerIds );
		if (!$ok) {
			$database->query( 'ROLLBACK;' );
			return FALSE;
		}*/
		
		// get weight classes
		//$weightClassIds = $this->getWeightClassIds( $database );
		
		// get length classes
		//$lengthClassIds = $this->getLengthClassIds( $database );
		
		// generate and execute SQL for storing the products
		foreach ($products as $product) {
			$productId = $product[0];
			$productName = addslashes($product[1]);
			$packet = $product[2];
			$lab = $product[3];
			$model = $product[4];
			$share = $product[5];
			$weight = $product[6];
			$colour = $product[7];
			$clarity = $product[8];
			$cut = $product[9];
			$polish = $product[10];
			$sym = $product[11];
			$flour = $product[12];
			$price = $product[13];
			$avgdia = $product[14];
			$mindia = $product[15];
			$maxdia = $product[16];
			$depth = $product[17];
			$table = $product[18];
			$dmm = $product[19];
			$tint = $product[20];
			$image = $product[21];
			$date_added = $product[22];
			$date_modified = $product[23];
			
			$this->db->from('product');
			$this->db->where('model', $model);
			$product_ids = $this->db->get();
			
			foreach ($product_ids->result() as $product_id) {
				$productid = $product_id->product_id;
			}
					
		$this->db->trans_start();			
		
		$basic_data = array(
			"model"	=> $model,
			"image"	=> $image,
			"weight"	=> $weight,
			"packet"	=> $packet,
			"price"	=> (float)$price,
			"status" => '1',
			"date_added"	=> date("Y-m-d H:i:s"),
		);
		
		$this->db->where('product_id', (int)$productid);
		$this->db->update('product', $basic_data);
		
		$product_id = $this->db->insert_id();
		
		$basic_data = array(
			"name"			=> $productName,
		);

		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->update('product_description', $basic_data);
		
		$basic_data = array(
			"customer_group_id"		=> 12,
			"points"		=> $price/100,
		);
					
		$this->db->where('product_id', (int)$productid);
		$this->db->update('product_reward', $basic_data);
			
		$basic_data = array(
			"customer_group_id"		=> 8,
			"points"		=> $price/100,
		);
					
		$this->db->where('product_id', (int)$productid);
		$this->db->update('product_reward', $basic_data);
					
		$basic_data = array(
			"customer_group_id"		=> 13,
			"points"		=> $price/100,
		);
		
		$this->db->where('product_id', (int)$productid);
		$this->db->update('product_reward', $basic_data);
		
		$basic_data = array(
			"language_id"	=> 3,
			"name"			=> $productName,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->update('product_description', $basic_data);
		
		if($lab!=''){
			$basic_data = array(
			"attribute_id"	=> '4',
			"text"			=> $lab,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 4);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($share!=''){
			$basic_data = array(
			"attribute_id"	=> '5',
			"text"			=> $share,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 5);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($colour!=''){
			$basic_data = array(
			"attribute_id"	=> '7',
			"text"			=> $colour,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 7);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($clarity!=''){
			$basic_data = array(
			"attribute_id"	=> '8',
			"text"			=> $clarity,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 8);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($cut!=''){
			$basic_data = array(
			"attribute_id"	=> '3',
			"text"			=> $cut,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 3);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($polish!=''){
			$basic_data = array(
			"attribute_id"	=> '9',
			"text"			=> $polish,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 9);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($sym!=''){
			$basic_data = array(
			"attribute_id"	=> '10',
			"text"			=> $sym,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 10);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($flour!=''){
			$basic_data = array(
			"attribute_id"	=> '11',
			"text"			=> $flour,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 11);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($avgdia!=''){
			$basic_data = array(
			"attribute_id"	=> '12',
			"text"			=> $avgdia,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 12);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($mindia!=''){
			$basic_data = array(
			"attribute_id"	=> '13',
			"text"			=> $mindia,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 13);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($maxdia!=''){
			$basic_data = array(
			"attribute_id"	=> '14',
			"text"			=> $maxdia,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 14);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($depth!=''){
			$basic_data = array(
			"attribute_id"	=> '15',
			"text"			=> $depth,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 15);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($table!=''){
			$basic_data = array(
			"attribute_id"	=> '16',
			"text"			=> $table,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 16);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($dmm!=''){
			$basic_data = array(
			"attribute_id"	=> '17',
			"text"			=> $dmm,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 17);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($tint!=''){
			$basic_data = array(
			"attribute_id"	=> '18',
			"text"			=> $tint,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', (int)$languageId);
		$this->db->where('attribute_id', 18);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($lab!=''){
			$basic_data = array(
			"attribute_id"	=> '4',
			"text"			=> $lab,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 4);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($share!=''){
			$basic_data = array(
			"attribute_id"	=> '5',
			"text"			=> $share,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 5);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($carats!=''){
			$basic_data = array(
			"attribute_id"	=> '6',
			"text"			=> $carats,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 6);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($colour!=''){
			$basic_data = array(
			"attribute_id"	=> '7',
			"text"			=> $colour,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 7);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($clarity!=''){
			$basic_data = array(
			"attribute_id"	=> '8',
			"text"			=> $clarity,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 8);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($cut!=''){
			$basic_data = array(
			"attribute_id"	=> '3',
			"text"			=> $cut,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 3);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($polish!=''){
			$basic_data = array(
			"attribute_id"	=> '9',
			"text"			=> $polish,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 9);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($sym!=''){
			$basic_data = array(
			"attribute_id"	=> '10',
			"text"			=> $sym,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 10);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($flour!=''){
			$basic_data = array(
			"attribute_id"	=> '11',
			"text"			=> $flour,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 11);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($avgdia!=''){
			$basic_data = array(
			"attribute_id"	=> '12',
			"text"			=> $avgdia,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 12);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($mindia!=''){
			$basic_data = array(
			"attribute_id"	=> '13',
			"text"			=> $mindia,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 13);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($maxdia!=''){
			$basic_data = array(
			"attribute_id"	=> '14',
			"text"			=> $maxdia,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 14);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($depth!=''){
			$basic_data = array(
			"attribute_id"	=> '15',
			"text"			=> $depth,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 15);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($table!=''){
			$basic_data = array(
			"attribute_id"	=> '16',
			"text"			=> $table,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 16);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($dmm!=''){
			$basic_data = array(
			"attribute_id"	=> '17',
			"text"			=> $dmm,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 17);
		$this->db->update('product_attribute', $basic_data);
		}
		
		if($tint!=''){
			$basic_data = array(
			"attribute_id"	=> '18',
			"text"			=> $tint,
		);
		$this->db->where('product_id', (int)$productid);
		$this->db->where('language_id', 3);
		$this->db->where('attribute_id', 18);
		$this->db->update('product_attribute', $basic_data);
		}

		}
		
		// final commit
		$database->query("COMMIT;");
		return TRUE;
	}
	
	function storeProductsUpdataDatabase( &$database, &$products ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove products
		$sql = "START TRANSACTION;\n";
		//$sql .= "DELETE FROM `product`;\n";
		//$sql .= "DELETE FROM `product_description` WHERE language_id=$languageId;\n";
		//$sql .= "DELETE FROM `product_to_category`;\n";
		//$sql .= "DELETE FROM `product_attribute`;\n";
		//$sql .= "DELETE FROM `product_to_store`;\n";
		//$sql .= "DELETE FROM `manufacturer_to_store`;\n";
		//$sql .= "DELETE FROM `url_alias` WHERE `query` LIKE 'product_id=%';\n";
		//$sql .= "DELETE FROM `product_related`;\n";
		//$sql .= "DELETE FROM `product_image`;\n";
		//$sql .= "DELETE FROM `product_tags` WHERE language_id=$languageId;\n";
		$this->import( $database, $sql );
		
		// store or update manufacturers
		/*$manufacturerIds = array();
		$ok = $this->storeManufacturersIntoDatabase( $database, $products, $manufacturerIds );
		if (!$ok) {
			$database->query( 'ROLLBACK;' );
			return FALSE;
		}*/
		
		// get weight classes
		//$weightClassIds = $this->getWeightClassIds( $database );
		
		// get length classes
		//$lengthClassIds = $this->getLengthClassIds( $database );
		
		// generate and execute SQL for storing the products

		foreach ($products as $product) {
			$productId = $product[0];
			$productName = addslashes($product[1]);
			$packet = $product[2];
			$lab = $product[3];
			$model = $product[4];
			$share = $product[5];
			$weight = $product[6];
			$colour = $product[7];
			$clarity = $product[8];
			$cut = $product[9];
			$polish = $product[10];
			$sym = $product[11];
			$flour = $product[12];
			$price = $product[13];
			$avgdia = $product[14];
			$mindia = $product[15];
			$maxdia = $product[16];
			$depth = $product[17];
			$table = $product[18];
			$dmm = $product[19];
			$tint = $product[20];
			$image = $product[21];
			$date_added = $product[22];
			$date_modified = $product[23];
			
			$productidid = 0;
			
			$this->db->from('product');
			$this->db->where('model', $model);
			$product_ids = $this->db->get();
			
			foreach ($product_ids->result() as $productid) {
				$productidid = $productid->product_id;
			}
			
			if($productidid!=0){
				$this->db->from('product');
				$this->db->where('quantity', '1');
				$this->db->where('model', $model);
				$quantityss = $this->db->get();
				$quantity =0;
				foreach ($quantityss->result() as $quantitys) {
					$quantity = $quantitys->quantity;
				}
				if($quantity!=0){
					$this->db->trans_start();			
		
					$basic_data = array(
						"price"	=> (float)$price,
					);
					
					$this->db->where('product_id', (int)$productidid);
					$this->db->update('product', $basic_data);
				}
			}else{
			
				$this->db->trans_start();			
		
				$basic_data = array(
					"image"	=> $image,
					"quantity"	=> '1',
					"model"	=> $model,
					"weight"	=> $weight,
					"packet"	=> $packet,
					"price"	=> (float)$price,
					"status" => '1',
					"date_added"	=> date("Y-m-d H:i:s"),
				);
				
				$this->db->insert('product', $basic_data);
				
				$product_id = $this->db->insert_id();
				
				$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"name"			=> $productName,
				);
				$this->db->insert('product_description', $basic_data);
				
				$basic_data = array(
					"product_id"		=> (int)$product_id,
					"customer_group_id"		=> 12,
					"points"		=> $price/100,
				);
							
				$this->db->insert('product_reward', $basic_data);
					
				$basic_data = array(
					"product_id"		=> (int)$product_id,
					"customer_group_id"		=> 8,
					"points"		=> $price/100,
				);
							
				$this->db->insert('product_reward', $basic_data);
							
				$basic_data = array(
					"product_id"		=> (int)$product_id,
					"customer_group_id"		=> 13,
					"points"		=> $price/100,
				);
				
				$this->db->insert('product_reward', $basic_data);
				
				$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"name"			=> $productName,
				);
				$this->db->insert('product_description', $basic_data);
				
				$basic_data = array(
					"product_id"	=> (int)$product_id,
					"store_id"	=> '0',
				);
				$this->db->insert('product_to_store', $basic_data);
				
				/*for($i=1;$i<=7;$i++){
					$basic_data = array(
						"product_id"	=> (int)$product_id,
						"category_id"	=> (int)$i,
					);
					$this->db->insert('product_to_category', $basic_data);
				}*/
				
				$basic_data = array(
					"product_id"	=> (int)$product_id,
					"category_id"	=> '1',
				);
				$this->db->insert('product_to_category', $basic_data);
				
				if($lab!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '4',
					"text"			=> $lab,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($share!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '5',
					"text"			=> $share,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($colour!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '7',
					"text"			=> $colour,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($clarity!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '8',
					"text"			=> $clarity,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($cut!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '3',
					"text"			=> $cut,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($polish!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '9',
					"text"			=> $polish,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($sym!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '10',
					"text"			=> $sym,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($flour!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '11',
					"text"			=> $flour,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($avgdia!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '12',
					"text"			=> $avgdia,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($mindia!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '13',
					"text"			=> $mindia,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($maxdia!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '14',
					"text"			=> $maxdia,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($depth!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '15',
					"text"			=> $depth,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($table!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '16',
					"text"			=> $table,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($dmm!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '17',
					"text"			=> $dmm,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($tint!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> (int)$languageId,
					"attribute_id"	=> '18',
					"text"			=> $tint,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($lab!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '4',
					"text"			=> $lab,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($share!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '5',
					"text"			=> $share,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($carats!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '6',
					"text"			=> $carats,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($colour!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '7',
					"text"			=> $colour,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($clarity!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '8',
					"text"			=> $clarity,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($cut!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '3',
					"text"			=> $cut,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($polish!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '9',
					"text"			=> $polish,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($sym!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '10',
					"text"			=> $sym,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($flour!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '11',
					"text"			=> $flour,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($avgdia!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '12',
					"text"			=> $avgdia,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($mindia!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '13',
					"text"			=> $mindia,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($maxdia!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '14',
					"text"			=> $maxdia,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($depth!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '15',
					"text"			=> $depth,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($table!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '16',
					"text"			=> $table,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($dmm!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '17',
					"text"			=> $dmm,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
				
				if($tint!=''){
					$basic_data = array(
					"product_id"	=> (int)$product_id,
					"language_id"	=> 3,
					"attribute_id"	=> '18',
					"text"			=> $tint,
				);
				$this->db->insert('product_attribute', $basic_data);
				}
			}
		}
		
		// final commit
		$database->query("COMMIT;");
		return TRUE;
	}

	protected function detect_encoding( $str ) {
		// auto detect the character encoding of a string
		return mb_detect_encoding( $str, 'UTF-8,ISO-8859-15,ISO-8859-1,cp1251,KOI8-R' );
	}


	function uploadProducts( &$reader, &$database ) {
		// find the default language id and default units
		$languageId = $this->getDefaultLanguageId($database);
		$defaultWeightUnit = $this->getDefaultWeightUnit();
		$defaultMeasurementUnit = $this->getDefaultMeasurementUnit();
		//$defaultStockStatusId = $this->config->get('config_stock_status_id');
		
		$data = $reader->sheets[0];
		$products = array();
		$product = array();
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[1]) ? $row[1] : "");
			if ($productId=="") {
				continue;
			}
			
			$name = isset($row[2]) ? $row[2] :"";
			$packet = isset($row[3]) ? $row[3] :"";
			$lab = isset($row[4]) ? $row[4] :"";
			$model = isset($row[5]) ? $row[5] :"";
			$share = isset($row[6]) ? $row[6] :"";
			$weight = isset($row[7]) ? $row[7] :"";
			$colour = isset($row[8]) ? $row[8] :"";
			$clarity = isset($row[9]) ? $row[9] :"";
			$cut = isset($row[10]) ? $row[10] :"";
			$polish = isset($row[11]) ? $row[11] :"";
			$sym = isset($row[12]) ? $row[12] :"";
			$flour = isset($row[13]) ? $row[13] :"";
			$price = isset($row[14]) ? $row[14] : "0.00";
			$avgdia = isset($row[15]) ? $row[15] :"";
			$mindia = isset($row[16]) ? $row[16] :"";
			$maxdia = isset($row[17]) ? $row[17] :"";
			$depth = isset($row[18]) ? $row[18] :"";
			$table = isset($row[19]) ? $row[19] :"";
			$dmm = isset($row[20]) ? $row[20] :"";
			$tint = isset($row[21]) ? $row[21] :"";
			$iamge = isset($row[22]) ? $row[22] :"";
			$dateAdded = (isset($row[23]) && (is_string($row[23])) && (strlen($row[23])>0)) ? $row[23] : "NOW()";
			$dateModified = (isset($row[24]) && (is_string($row[24])) && (strlen($row[24])>0)) ? $row[24] : "NOW()";
			
			$product = array();
			$product[0] = $productId;
			$product[1] = $name;
			$product[2] = $packet;
			$product[3] = $lab;
			$product[4] = $model;
			$product[5] = $share;
			$product[6] = $weight;
			$product[7] = $colour;
			$product[8] = $clarity;
			$product[9] = $cut;
			$product[10] = $polish;
			$product[11] = $sym;
			$product[12] = $flour;
			$product[13] = $price;
			$product[14] = $avgdia;
			$product[15] = $mindia;
			$product[16] = $maxdia;
			$product[17] = $depth;
			$product[18] = $table;
			$product[19] = $dmm;
			$product[20] = $tint;
			$product[21] = $iamge;
			$product[22] = $dateAdded;
			$product[23] = $dateModified;
			/*$name = isset($row[2]) ? $row[2] :"";
			$lab = isset($row[3]) ? $row[3] :"";
			$model = isset($row[4]) ? $row[4] :"";
			$share = isset($row[5]) ? $row[5] :"";
			$carats = isset($row[6]) ? $row[6] :"";
			$colour = isset($row[7]) ? $row[7] :"";
			$clarity = isset($row[8]) ? $row[8] :"";
			$cut = isset($row[9]) ? $row[9] :"";
			$polish = isset($row[10]) ? $row[10] :"";
			$sym = isset($row[11]) ? $row[11] :"";
			$flour = isset($row[12]) ? $row[12] :"";
			$price = isset($row[13]) ? $row[13] : "0.00";
			$avgdia = isset($row[14]) ? $row[14] :"";
			$maxdia = isset($row[15]) ? $row[15] :"";
			$mindia = isset($row[16]) ? $row[16] :"";
			$depth = isset($row[17]) ? $row[17] :"";
			$table = isset($row[18]) ? $row[18] :"";
			$dmm = isset($row[19]) ? $row[19] :"";
			$tint = isset($row[20]) ? $row[20] :"";
			$dateAdded = (isset($row[21]) && (is_string($row[21])) && (strlen($row[21])>0)) ? $row[21] : "NOW()";
			$dateModified = (isset($row[22]) && (is_string($row[22])) && (strlen($row[22])>0)) ? $row[22] : "NOW()";

			$product = array();
			$product[0] = $productId;
			$product[1] = $name;
			$product[2] = $lab;
			$product[3] = $model;
			$product[4] = $share;
			$product[5] = $carats;
			$product[6] = $colour;
			$product[7] = $clarity;
			$product[8] = $cut;
			$product[9] = $polish;
			$product[10] = $sym;
			$product[11] = $flour;
			$product[12] = $price;
			$product[13] = $avgdia;
			$product[14] = $mindia;
			$product[15] = $maxdia;
			$product[16] = $depth;
			$product[17] = $table;
			$product[18] = $dmm;
			$product[19] = $tint;
			$product[20] = $dateAdded;
			$product[21] = $dateModified;*/
			
			$products[$productId] = $product;
		}
		return $this->storeProductsIntoDatabase( $database, $products );
	}
	
	function editProducts( &$reader, &$database ) {
		// find the default language id and default units
		$languageId = $this->getDefaultLanguageId($database);
		$defaultWeightUnit = $this->getDefaultWeightUnit();
		$defaultMeasurementUnit = $this->getDefaultMeasurementUnit();
		//$defaultStockStatusId = $this->config->get('config_stock_status_id');
		
		$data = $reader->sheets[0];
		$products = array();
		$product = array();
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[1]) ? $row[1] : "");
			if ($productId=="") {
				continue;
			}
			
			$name = isset($row[2]) ? $row[2] :"";
			$packet = isset($row[3]) ? $row[3] :"";
			$lab = isset($row[4]) ? $row[4] :"";
			$model = isset($row[5]) ? $row[5] :"";
			$share = isset($row[6]) ? $row[6] :"";
			$weight = isset($row[7]) ? $row[7] :"";
			$colour = isset($row[8]) ? $row[8] :"";
			$clarity = isset($row[9]) ? $row[9] :"";
			$cut = isset($row[10]) ? $row[10] :"";
			$polish = isset($row[11]) ? $row[11] :"";
			$sym = isset($row[12]) ? $row[12] :"";
			$flour = isset($row[13]) ? $row[13] :"";
			$price = isset($row[14]) ? $row[14] : "0.00";
			$avgdia = isset($row[15]) ? $row[15] :"";
			$mindia = isset($row[16]) ? $row[16] :"";
			$maxdia = isset($row[17]) ? $row[17] :"";
			$depth = isset($row[18]) ? $row[18] :"";
			$table = isset($row[19]) ? $row[19] :"";
			$dmm = isset($row[20]) ? $row[20] :"";
			$tint = isset($row[21]) ? $row[21] :"";
			$image = isset($row[22]) ? $row[22] :"";
			$dateAdded = (isset($row[23]) && (is_string($row[23])) && (strlen($row[23])>0)) ? $row[23] : "NOW()";
			$dateModified = (isset($row[24]) && (is_string($row[24])) && (strlen($row[24])>0)) ? $row[24] : "NOW()";
			
			$product = array();
			$product[0] = $productId;
			$product[1] = $name;
			$product[2] = $packet;
			$product[3] = $lab;
			$product[4] = $model;
			$product[5] = $share;
			$product[6] = $weight;
			$product[7] = $colour;
			$product[8] = $clarity;
			$product[9] = $cut;
			$product[10] = $polish;
			$product[11] = $sym;
			$product[12] = $flour;
			$product[13] = $price;
			$product[14] = $avgdia;
			$product[15] = $mindia;
			$product[16] = $maxdia;
			$product[17] = $depth;
			$product[18] = $table;
			$product[19] = $dmm;
			$product[20] = $tint;
			$product[21] = $image;
			$product[22] = $dateAdded;
			$product[23] = $dateModified;
			
			$products[$productId] = $product;
		}
		return $this->storeProductsSetDatabase( $database, $products );
	}
	
	function updataProducts( &$reader, &$database ) {
		// find the default language id and default units
		$languageId = $this->getDefaultLanguageId($database);
		$defaultWeightUnit = $this->getDefaultWeightUnit();
		$defaultMeasurementUnit = $this->getDefaultMeasurementUnit();
		//$defaultStockStatusId = $this->config->get('config_stock_status_id');
		
		$data = $reader->sheets[0];
		$products = array();
		$product = array();
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[1]) ? $row[1] : "");
			if ($productId=="") {
				continue;
			}
			
			$name = isset($row[2]) ? $row[2] :"";
			$packet = isset($row[3]) ? $row[3] :"";
			$lab = isset($row[4]) ? $row[4] :"";
			$model = isset($row[5]) ? $row[5] :"";
			$share = isset($row[6]) ? $row[6] :"";
			$weight = isset($row[7]) ? $row[7] :"";
			$colour = isset($row[8]) ? $row[8] :"";
			$clarity = isset($row[9]) ? $row[9] :"";
			$cut = isset($row[10]) ? $row[10] :"";
			$polish = isset($row[11]) ? $row[11] :"";
			$sym = isset($row[12]) ? $row[12] :"";
			$flour = isset($row[13]) ? $row[13] :"";
			$price = isset($row[14]) ? $row[14] : "0.00";
			$avgdia = isset($row[15]) ? $row[15] :"";
			$mindia = isset($row[16]) ? $row[16] :"";
			$maxdia = isset($row[17]) ? $row[17] :"";
			$depth = isset($row[18]) ? $row[18] :"";
			$table = isset($row[19]) ? $row[19] :"";
			$dmm = isset($row[20]) ? $row[20] :"";
			$tint = isset($row[21]) ? $row[21] :"";
			$image = isset($row[22]) ? $row[22] :"";
			$dateAdded = (isset($row[23]) && (is_string($row[23])) && (strlen($row[23])>0)) ? $row[23] : "NOW()";
			$dateModified = (isset($row[24]) && (is_string($row[24])) && (strlen($row[24])>0)) ? $row[24] : "NOW()";
			
			$product = array();
			$product[0] = $productId;
			$product[1] = $name;
			$product[2] = $packet;
			$product[3] = $lab;
			$product[4] = $model;
			$product[5] = $share;
			$product[6] = $weight;
			$product[7] = $colour;
			$product[8] = $clarity;
			$product[9] = $cut;
			$product[10] = $polish;
			$product[11] = $sym;
			$product[12] = $flour;
			$product[13] = $price;
			$product[14] = $avgdia;
			$product[15] = $mindia;
			$product[16] = $maxdia;
			$product[17] = $depth;
			$product[18] = $table;
			$product[19] = $dmm;
			$product[20] = $tint;
			$product[21] = $image;
			$product[22] = $dateAdded;
			$product[23] = $dateModified;
			
			$products[$productId] = $product;
		}
		return $this->storeProductsUpdataDatabase( $database, $products );
	}


	function storeCategoriesIntoDatabase( &$database, &$categories ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);

		// start transaction, remove categories
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `category`;\n";
		$sql .= "DELETE FROM `category_description` WHERE language_id=$languageId;\n";
		$sql .= "DELETE FROM `category_to_store`;\n";
		$sql .= "DELETE FROM `url_alias` WHERE `query` LIKE 'category_id=%';\n";
		$this->import( $database, $sql );
		
		// generate and execute SQL for inserting the categories
		foreach ($categories as $category) {
			$categoryId = $category[0];
			$imageName = $category[1];
			$parentId = $category[2];
			$sortOrder = $category[3];
			$dateAdded = $category[4];
			$dateModified = $category[5];
			$languageId = $category[6];
			$name = addslashes($category[7]);
			$description = addslashes($category[8]);
			$meta_description = addslashes($category[9]);
			$meta_keywords = addslashes($category[10]);
			$keyword = addslashes($category[11]);
			$storeIds = $category[12];
			$status = $category[13];
			$status = ((strtoupper($status)=="TRUE") || (strtoupper($status)=="YES") || (strtoupper($status)=="ENABLED")) ? 1 : 0;
			$sql2 = "INSERT INTO `category` (`category_id`, `image`, `parent_id`, `sort_order`, `date_added`, `date_modified`, `status`) VALUES ";
			$sql2 .= "( $categoryId, '$imageName', $parentId, $sortOrder, ";
			$sql2 .= ($dateAdded=='NOW()') ? "$dateAdded," : "'$dateAdded',";
			$sql2 .= ($dateModified=='NOW()') ? "$dateModified," : "'$dateModified',";
			$sql2 .= " $status);";
			$database->query( $sql2 );
			$sql3 = "INSERT INTO `category_description` (`category_id`, `language_id`, `name`, `description`, `meta_description`, `meta_keywords`) VALUES ";
			$sql3 .= "( $categoryId, $languageId, '$name', '$description', '$meta_description', '$meta_keywords' );";
			$database->query( $sql3 );
			if ($keyword) {
				$sql5 = "INSERT INTO `url_alias` (`query`,`keyword`) VALUES ('category_id=$categoryId','$keyword');";
				$database->query($sql5);
			}
			foreach ($storeIds as $storeId) {
				$sql6 = "INSERT INTO `category_to_store` (`category_id`,`store_id`) VALUES ($categoryId,$storeId);";
				$database->query($sql6);
			}
		}
		
		// final commit
		$database->query( "COMMIT;" );
		return TRUE;
	}


	function uploadCategories( &$reader, &$database ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		$data = $reader->sheets[0];
		$categories = array();
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$categoryId = trim(isset($row[1]) ? $row[1] : "");
			if ($categoryId=="") {
				continue;
			}
			$parentId = isset($row[2]) ? $row[2] : "0";
			$name = isset($row[3]) ? $row[3] : "";
			$name = htmlentities( $name, ENT_QUOTES, $this->detect_encoding($name) );
			$sortOrder = isset($row[4]) ? $row[4] : "0";
			$imageName = trim(isset($row[5]) ? $row[5] : "");
			$dateAdded = (isset($row[6]) && (is_string($row[6])) && (strlen($row[6])>0)) ? $row[6] : "NOW()";
			$dateModified = (isset($row[7]) && (is_string($row[7])) && (strlen($row[7])>0)) ? $row[7] : "NOW()";
			$langId = isset($row[8]) ? $row[8] : "1";
			if ($langId != $languageId) {
				continue;
			}
			$keyword = isset($row[9]) ? $row[9] : "";
			$description = isset($row[10]) ? $row[10] : "";
			$description = htmlentities( $description, ENT_QUOTES, $this->detect_encoding($description) );
			$meta_description = isset($row[11]) ? $row[11] : "";
			$meta_description = htmlentities( $meta_description, ENT_QUOTES, $this->detect_encoding($meta_description) );
			$meta_keywords = isset($row[12]) ? $row[12] : "";
			$meta_keywords = htmlentities( $meta_keywords, ENT_QUOTES, $this->detect_encoding($meta_keywords) );
			$storeIds = isset($row[13]) ? $row[13] : "";
			$status = isset($row[14]) ? $row[14] : "true";
			$category = array();
			$category[0] = $categoryId;
			$category[1] = $imageName;
			$category[2] = $parentId;
			$category[3] = $sortOrder;
			$category[4] = $dateAdded;
			$category[5] = $dateModified;
			$category[6] = $languageId;
			$category[7] = $name;
			$category[8] = $description;
			$category[9] = $meta_description;
			$category[10] = $meta_keywords;
			$category[11] = $keyword;
			$storeIds = trim( $this->clean($storeIds, FALSE) );
			$category[12] = ($storeIds=="") ? array() : explode( ",", $storeIds );
			if ($category[12]===FALSE) {
				$category[12] = array();
			}
			$category[13] = $status;
			$categories[$categoryId] = $category;
		}
		return $this->storeCategoriesIntoDatabase( $database, $categories );
	}


	function storeOptionNamesIntoDatabase( &$database, &$options, &$optionIds )
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);

		// add option names, ids, and sort orders to the database
		$maxOptionId = 0;
		$sortOrder = 0;
		$sql = "INSERT INTO `product_option` (`product_option_id`, `product_id`, `sort_order`) VALUES "; 
		$sql2 = "INSERT INTO `product_option_description` (`product_option_id`, `product_id`, `language_id`, `name`) VALUES ";
		$k = strlen( $sql );
		$first = TRUE;
		foreach ($options as $option) {
			$productId = $option['product_id'];
			$name = $option['option'];
			$langId = $option['language_id'];
			if ($productId=="") {
				continue;
			}
			if ($langId != $languageId) {
				continue;
			}
			if ($name=="") {
				continue;
			}
			if (!isset($optionIds[$productId][$name])) {
				$maxOptionId += 1;
				$optionId = $maxOptionId;
				if (!isset($optionIds[$productId])) {
					$optionIds[$productId] = array();
					$sortOrder = 0;
				}
				$sortOrder += 1;
				$optionIds[$productId][$name] = $optionId;
				$sql .= ($first) ? "\n" : ",\n";
				$sql2 .= ($first) ? "\n" : ",\n";
				$first = FALSE;
				$sql .= "($optionId, $productId, $sortOrder )";
				$sql2 .= "($optionId, $productId, $languageId, '$name' )";
			}
		}
		$sql .= ";\n";
		$sql2 .= ";\n";
		if (strlen( $sql ) > $k+2) {
			$database->query( $sql );
			$database->query( $sql2 );
		}
		return TRUE;
	}



	function storeOptionDetailsIntoDatabase( &$database, &$options, &$optionIds )
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);

		// generate SQL for storing all the option details into the database
		$sql = "INSERT INTO `product_option_value` (`product_option_value_id`, `product_id`, `product_option_id`, `quantity`, `subtract`, `price`, `prefix`, `sort_order`) VALUES "; 
		$sql2 = "INSERT INTO `product_option_value_description` (`product_option_value_id`, `product_id`, `language_id`, `name`) VALUES ";
		$k = strlen( $sql );
		$first = TRUE;
		foreach ($options as $index => $option) {
			$productOptionValueId = $index+1;
			$productId = $option['product_id'];
			$optionName = $option['option'];
			$optionId = $optionIds[$productId][$optionName];
			$optionValue = $option['option_value'];
			$quantity = $option['quantity'];
			$subtract = $option['subtract'];
			$subtract = ((strtoupper($subtract)=="TRUE") || (strtoupper($subtract)=="YES") || (strtoupper($subtract)=="ENABLED")) ? 1 : 0;
			$price = $option['price'];
			$prefix = $option['prefix'];
			$sortOrder = $option['sort_order'];
			$sql .= ($first) ? "\n" : ",\n";
			$sql2 .= ($first) ? "\n" : ",\n";
			$first = FALSE;
			$sql .= "($productOptionValueId, $productId, $optionId, $quantity, $subtract, $price, '$prefix', $sortOrder)";
			$sql2 .= "($productOptionValueId, $productId, $languageId, '$optionValue')";
		}
		$sql .= ";\n";
		$sql2 .= ";\n";
		
		// execute the database query
		if (strlen( $sql ) > $k+2) {
			$database->query( $sql );
			$database->query( $sql2 );
		}
		return TRUE;
	}


	function storeOptionsIntoDatabase( &$database, &$options ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// start transaction, remove options
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `product_option`;\n";
		$sql .= "DELETE FROM `product_option_description` WHERE language_id=$languageId;\n";
		$sql .= "DELETE FROM `product_option_value`;\n";
		$sql .= "DELETE FROM `product_option_value_description` WHERE language_id=$languageId;\n";
		$this->import( $database, $sql );
		
		// store option names
		$optionIds = array(); // indexed by product_id and name
		$ok = $this->storeOptionNamesIntoDatabase( $database, $options, $optionIds );
		if (!$ok) {
			$database->query( 'ROLLBACK;' );
			return FALSE;
		}
		
		// store option details
		$ok = $this->storeOptionDetailsIntoDatabase( $database, $options, $optionIds );
		if (!$ok) {
			$database->query( 'ROLLBACK;' );
			return FALSE;
		}
		
		$database->query("COMMIT;");
		return TRUE;
	}



	function uploadOptions( &$reader, &$database ) 
	{
		$data = $reader->sheets[2];
		$options = array();
		$i = 0;
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[1]) ? $row[1] : "");
			if ($productId=="") {
				continue;
			}
			$languageId = isset($row[2]) ? $row[2] : "";
			$option = isset($row[3]) ? $row[3] : "";
			$optionValue = isset($row[4]) ? $row[4] : "";
			$optionQuantity = isset($row[5]) ? $row[5] : "0";
			$optionSubtract = isset($row[6]) ? $row[6] : "false";
			$optionPrice = isset($row[7]) ? $row[7] : "0";
			$optionPrefix = isset($row[8]) ? $row[8] : "+";
			$sortOrder = isset($row[9]) ? $row[9] : "0";
			$options[$i] = array();
			$options[$i]['product_id'] = $productId;
			$options[$i]['language_id'] = $languageId;
			$options[$i]['option'] = $option;
			$options[$i]['option_value'] = $optionValue;
			$options[$i]['quantity'] = $optionQuantity;
			$options[$i]['subtract'] = $optionSubtract;
			$options[$i]['price'] = $optionPrice;
			$options[$i]['prefix'] = $optionPrefix;
			$options[$i]['sort_order'] = $sortOrder;
			$i += 1;
		}
		return $this->storeOptionsIntoDatabase( $database, $options );
	}



	function storeSpecialsIntoDatabase( &$database, &$specials )
	{
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `product_special`;\n";
		$this->import( $database, $sql );

		// find existing customer groups from the database
		$sql = "SELECT * FROM `customer_group`";
		$result = $database->query( $sql );
		$maxCustomerGroupId = 0;
		$customerGroups = array();
		foreach ($result->rows as $row) {
			$customerGroupId = $row['customer_group_id'];
			$name = $row['name'];
			if (!isset($customerGroups[$name])) {
				$customerGroups[$name] = $customerGroupId;
			}
			if ($maxCustomerGroupId < $customerGroupId) {
				$maxCustomerGroupId = $customerGroupId;
			}
		}

		// add additional customer groups into the database
		foreach ($specials as $special) {
			$name = $special['customer_group'];
			if (!isset($customerGroups[$name])) {
				$maxCustomerGroupId += 1;
				$sql  = "INSERT INTO `customer_group` (`customer_group_id`, `name`) VALUES "; 
				$sql .= "($maxCustomerGroupId, '$name')";
				$sql .= ";\n";
				$database->query($sql);
				$customerGroups[$name] = $maxCustomerGroupId;
			}
		}

		// store product specials into the database
		$productSpecialId = 0;
		$first = TRUE;
		$sql = "INSERT INTO `product_special` (`product_special_id`,`product_id`,`customer_group_id`,`priority`,`price`,`date_start`,`date_end` ) VALUES "; 
		foreach ($specials as $special) {
			$productSpecialId += 1;
			$productId = $special['product_id'];
			$name = $special['customer_group'];
			$customerGroupId = $customerGroups[$name];
			$priority = $special['priority'];
			$price = $special['price'];
			$dateStart = $special['date_start'];
			$dateEnd = $special['date_end'];
			$sql .= ($first) ? "\n" : ",\n";
			$first = FALSE;
			$sql .= "($productSpecialId,$productId,$customerGroupId,$priority,$price,'$dateStart','$dateEnd')";
		}
		if (!$first) {
			$database->query($sql);
		}

		$database->query("COMMIT;");
		return TRUE;
	}


	function uploadSpecials( &$reader, &$database ) 
	{
		$data = $reader->sheets[3];
		$specials = array();
		$i = 0;
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[1]) ? $row[1] : "");
			if ($productId=="") {
				continue;
			}
			$customerGroup = trim(isset($row[2]) ? $row[2] : "");
			if ($customerGroup=="") {
				continue;
			}
			$priority = isset($row[3]) ? $row[3] : "0";
			$price = isset($row[4]) ? $row[4] : "0";
			$dateStart = isset($row[5]) ? $row[5] : "0000-00-00";
			$dateEnd = isset($row[6]) ? $row[6] : "0000-00-00";
			$specials[$i] = array();
			$specials[$i]['product_id'] = $productId;
			$specials[$i]['customer_group'] = $customerGroup;
			$specials[$i]['priority'] = $priority;
			$specials[$i]['price'] = $price;
			$specials[$i]['date_start'] = $dateStart;
			$specials[$i]['date_end'] = $dateEnd;
			$i += 1;
		}
		return $this->storeSpecialsIntoDatabase( $database, $specials );
	}


	function storeDiscountsIntoDatabase( &$database, &$discounts )
	{
		$sql = "START TRANSACTION;\n";
		$sql .= "DELETE FROM `product_discount`;\n";
		$this->import( $database, $sql );

		// find existing customer groups from the database
		$sql = "SELECT * FROM `customer_group`";
		$result = $database->query( $sql );
		$maxCustomerGroupId = 0;
		$customerGroups = array();
		foreach ($result->rows as $row) {
			$customerGroupId = $row['customer_group_id'];
			$name = $row['name'];
			if (!isset($customerGroups[$name])) {
				$customerGroups[$name] = $customerGroupId;
			}
			if ($maxCustomerGroupId < $customerGroupId) {
				$maxCustomerGroupId = $customerGroupId;
			}
		}

		// add additional customer groups into the database
		foreach ($discounts as $discount) {
			$name = $discount['customer_group'];
			if (!isset($customerGroups[$name])) {
				$maxCustomerGroupId += 1;
				$sql  = "INSERT INTO `customer_group` (`customer_group_id`, `name`) VALUES "; 
				$sql .= "($maxCustomerGroupId, '$name')";
				$sql .= ";\n";
				$database->query($sql);
				$customerGroups[$name] = $maxCustomerGroupId;
			}
		}

		// store product discounts into the database
		$productDiscountId = 0;
		$first = TRUE;
		$sql = "INSERT INTO `product_discount` (`product_discount_id`,`product_id`,`customer_group_id`,`quantity`,`priority`,`price`,`date_start`,`date_end` ) VALUES "; 
		foreach ($discounts as $discount) {
			$productDiscountId += 1;
			$productId = $discount['product_id'];
			$name = $discount['customer_group'];
			$customerGroupId = $customerGroups[$name];
			$quantity = $discount['quantity'];
			$priority = $discount['priority'];
			$price = $discount['price'];
			$dateStart = $discount['date_start'];
			$dateEnd = $discount['date_end'];
			$sql .= ($first) ? "\n" : ",\n";
			$first = FALSE;
			$sql .= "($productDiscountId,$productId,$customerGroupId,$quantity,$priority,$price,'$dateStart','$dateEnd')";
		}
		if (!$first) {
			$database->query($sql);
		}

		$database->query("COMMIT;");
		return TRUE;
	}


	function uploadDiscounts( &$reader, &$database ) 
	{
		$data = $reader->sheets[4];
		$discounts = array();
		$i = 0;
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[1]) ? $row[1] : "");
			if ($productId=="") {
				continue;
			}
			$customerGroup = trim(isset($row[2]) ? $row[2] : "");
			if ($customerGroup=="") {
				continue;
			}
			$quantity = isset($row[3]) ? $row[3] : "0";
			$priority = isset($row[4]) ? $row[4] : "0";
			$price = isset($row[5]) ? $row[5] : "0";
			$dateStart = isset($row[6]) ? $row[6] : "0000-00-00";
			$dateEnd = isset($row[7]) ? $row[7] : "0000-00-00";
			$discounts[$i] = array();
			$discounts[$i]['product_id'] = $productId;
			$discounts[$i]['customer_group'] = $customerGroup;
			$discounts[$i]['quantity'] = $quantity;
			$discounts[$i]['priority'] = $priority;
			$discounts[$i]['price'] = $price;
			$discounts[$i]['date_start'] = $dateStart;
			$discounts[$i]['date_end'] = $dateEnd;
			$i += 1;
		}
		return $this->storeDiscountsIntoDatabase( $database, $discounts );
	}



	function storeAdditionalImagesIntoDatabase( &$reader, &$database )
	{
		// start transaction
		$sql = "START TRANSACTION;\n";
		
		// delete old additional product images from database
		$sql = "DELETE FROM `product_image`";
		$database->query( $sql );
		
		// insert new additional product images into database
		$data = $reader->sheets[1];  // Products worksheet
		$isFirstRow = TRUE;
		$maxImageId = 0;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[1]) ? $row[1] : "");
			if ($productId=="") {
				continue;
			}
			$imageNames = trim(isset($row[31]) ? $row[31] : "");
			$imageNames = trim( $this->clean($imageNames, FALSE) );
			$imageNames = ($imageNames=="") ? array() : explode( ",", $imageNames );
			foreach ($imageNames as $imageName) {
				$maxImageId += 1;
				$sql = "INSERT INTO `product_image` (`product_image_id`, product_id, `image`) VALUES ";
				$sql .= "($maxImageId,$productId,'$imageName');";
				$database->query( $sql );
			}
		}
		
		$database->query( "COMMIT;" );
		return TRUE;
	}


	function uploadImages( &$reader, &$database )
	{
		$ok = $this->storeAdditionalImagesIntoDatabase( $reader, $database );
		return $ok;
	}


	function storeGlobalOptionNamesIntoDatabase( &$database, &$options, &$optionIds )
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);

		// add option names, ids, and sort orders to the database
		$sql_max_id = "SELECT MAX(product_option_id) AS mid FROM `product_option`";
		$result_mid = $database->query( $sql_max_id );
		$maxOptionId = $result_mid->rows[0]['mid'];
		$optionId = $maxOptionId;
		
		$sql = "INSERT INTO `product_option` (`product_option_id`, `product_id`, `global_option_id`) VALUES ";
		$k = strlen( $sql );
		$first = TRUE;
		foreach ($options as $option) {
			$productId = $option['product_id'];
			$global_optionId = $option['option'];
			$langId = $option['language_id'];
			if ($productId=="") {
				continue;
			}
			if ($langId != $languageId) {
				continue;
			}
			if ($global_optionId=="") {
				continue;
			}
			if (!isset($optionIds[$productId][$global_optionId])) {
				$maxOptionId += 1;
				$optionId = $maxOptionId;
				if (!isset($optionIds[$productId])) {
					$optionIds[$productId] = array();
				}
				$optionIds[$productId][$global_optionId] = $optionId;
				$sql .= ($first) ? "\n" : ",\n";
				$first = FALSE;
				$sql .= "($optionId, $productId, $global_optionId )";
			}
		}
		$sql .= ";\n";
		if (strlen( $sql ) > $k+2) {
			$database->query( $sql );
		}
		return TRUE;
	}



	function storeGlobalOptionDetailsIntoDatabase( &$database, &$options, &$optionIds )
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);

		// generate SQL for storing all the option details into the database
		$sql_max_id = "SELECT MAX(product_option_value_id) AS mid FROM `product_option_value`";
		$result_mid = $database->query( $sql_max_id );
		$row = $result_mid->rows;
		$maxOptionValueId = $row[0]['mid'];
		
		foreach ($options as $index => $option)
		{
			$productOptionValueId = $index+1+$maxOptionValueId;
			$productId = $option['product_id'];
			$optionId = $option['option'];
			$global_optionValue = $option['option_value'];		
			$relative_optionId = $optionIds[$productId][$optionId];		
			$sql = "INSERT INTO `product_option_value` (`product_option_value_id`, `product_id`, `product_option_id`, `global_option_value_id`) VALUES ($productOptionValueId, $productId, $relative_optionId, '$global_optionValue')"; 
			$database->query( $sql );
			$relative_option_value_id = $database->getLastId();
			if($global_optionValue == 0)
			{
				$global_optionValue = $option['option_value_name'];
				$sql = "INSERT INTO `product_option_value_description` (`product_option_value_id`, `product_id`, `language_id`, `name`) VALUES ($relative_option_value_id, $productId, $languageId, '$global_optionValue')"; 
				$database->query( $sql );
			}
		}
		
		// execute the database query
		return TRUE;
	}


	function storeGlobalOptionsIntoDatabase( &$database, &$options ) 
	{
		// find the default language id
		$languageId = $this->getDefaultLanguageId($database);
		
		// store option names
		$optionIds = array(); // indexed by product_id and name
		$ok = $this->storeGlobalOptionNamesIntoDatabase( $database, $options, $optionIds );
		if (!$ok) {
			$database->query( 'ROLLBACK;' );
			return FALSE;
		}
		
		// store option details
		$ok = $this->storeGlobalOptionDetailsIntoDatabase( $database, $options, $optionIds );
		if (!$ok) {
			$database->query( 'ROLLBACK;' );
			return FALSE;
		}/**/
		
		$database->query("COMMIT;");
		return TRUE;
	}




	function uploadGlobalOptions( &$reader, &$database ) 
	{
		$data = $reader->sheets[5];
		$options = array();
		$i = 0;
		$isFirstRow = TRUE;
		foreach ($data['cells'] as $row) {
			if ($isFirstRow) {
				$isFirstRow = FALSE;
				continue;
			}
			$productId = trim(isset($row[3]) ? $row[3] : "");
			if ($productId=="") {
				continue;
			}
			$languageId = isset($row[5]) ? $row[4] : "";
			$option = isset($row[5]) ? $row[5] : "";
			$optionValue = isset($row[6]) ? $row[6] : "";
			$optionName = isset($row[7]) ? $row[7] : "";
			$optionValueName = isset($row[8]) ? $row[8] : "";
			$options[$i] = array();
			$options[$i]['product_id'] = $productId;
			$options[$i]['language_id'] = $languageId;
			$options[$i]['option'] = $option;
			$options[$i]['option_value'] = $optionValue;
			$options[$i]['option_name'] = $optionName;
			$options[$i]['option_value_name'] = $optionValueName;
			$i += 1;
		}
		return $this->storeGlobalOptionsIntoDatabase( $database, $options );
	}


	function validateHeading( &$data, &$expected ) {
		$heading = array();
		foreach ($data['cells'] as $row) {
			for ($i=1; $i<=count($expected); $i+=1) {
				$heading[] = isset($row[$i]) ? $row[$i] : "";
			}
			break;
		}
		$valid = TRUE;
		for ($i=0; $i < count($expected); $i+=1) {
			if (!isset($heading[$i])) {
				$valid = FALSE;
				break;
			}
			if (strtolower($heading[$i]) != strtolower($expected[$i])) {
				$valid = FALSE;
				break;
			}
		}
		return $valid;
	}



	function validateCategories( &$reader )
	{
		$expectedCategoryHeading = array
		( "category_id", "parent_id", "name", "sort_order", "image_name", "date_added", "date_modified", "language_id", "seo_keyword", "description", "meta_description", "meta_keywords", "store_ids", "status\nenabled" );
		$data =& $reader->sheets[0];
		return $this->validateHeading( $data, $expectedCategoryHeading );
	}


	function validateProducts( &$reader )
	{
		$expectedProductHeading = array
		( "product_id", "name", "categories", "sku", "location", "quantity", "model", "manufacturer", "image_name", "requires\nshipping", "price", "original_price", "special_price", "date_added", "date_modified", "date_available", "weight", "unit", "length", "width", "height", "length\nunit", "status\nenabled", "tax_class_id", "viewed", "language_id", "seo_keyword", "description", "meta_description", "meta_keywords", "additional image names", "stock_status_id", "store_ids", "related_ids", "tags", "sort_order", "subtract", "if_latest", "if_bestseller", "minimum", "cost" );
		$data = $reader->sheets[1];
		return $this->validateHeading( $data, $expectedProductHeading );
	}


	function validateOptions( &$reader )
	{
		$expectedOptionHeading = array
		( "product_id", "language_id", "option", "option_value", "quantity", "subtract", "price", "prefix", "sort_order" );
		$data = $reader->sheets[2];
		return $this->validateHeading( $data, $expectedOptionHeading );
	}


	function validateSpecials( &$reader )
	{
		$expectedSpecialsHeading = array
		( "product_id", "customer_group", "priority", "price", "date_start", "date_end" );
		$data = $reader->sheets[3];
		return $this->validateHeading( $data, $expectedSpecialsHeading );
	}


	function validateDiscounts( &$reader )
	{
		$expectedDiscountsHeading = array
		( "product_id", "customer_group", "quantity", "priority", "price", "date_start", "date_end" );
		$data = $reader->sheets[4];
		return $this->validateHeading( $data, $expectedDiscountsHeading );
	}


	function validateGlobalOptions( &$reader )
	{
		$expectedGlobalOptionHeading = array
		( "product_id", "language_id", "global_option_id", "global_option_value_id", "global_option", "global_option_value"  );
		$data = $reader->sheets[5];
		return $this->validateHeading( $data, $expectedDiscountsHeading );
	}


	function validateUpload( &$reader )
	{
		if (count($reader->sheets) != 6) {//add 5 to 6
			return FALSE;
		}
		if (!$this->validateCategories( $reader )) {
			return FALSE;
		}
		if (!$this->validateProducts( $reader )) {
			return FALSE;
		}
		if (!$this->validateOptions( $reader )) {
			return FALSE;
		}
		if (!$this->validateSpecials( $reader )) {
			return FALSE;
		}
		if (!$this->validateDiscounts( $reader )) {
			return FALSE;
		}
		if (!$this->validateGlobalOptions( $reader )) {
			return FALSE;
		}
		return TRUE;
	}


	function clearCache() {
/*		$this->cache->delete('category');
		$this->cache->delete('category_description');
		$this->cache->delete('manufacturer');
		$this->cache->delete('product');
		$this->cache->delete('product_image');
		$this->cache->delete('product_option');
		$this->cache->delete('product_option_description');
		$this->cache->delete('product_option_value');
		$this->cache->delete('product_option_value_description');
		$this->cache->delete('product_to_category');
		$this->cache->delete('url_alias');
		$this->cache->delete('product_special');
		$this->cache->delete('product_discount');*/
	}


	function upload( $filename ) {
		global $config;
		global $log;
		//$config = $this->config;
		//$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		$database =& $this->db;
		require_once 'pear/Spreadsheet/Excel/Reader.php';
		ini_set("memory_limit","512M");
		ini_set("max_execution_time",180);
		//set_time_limit( 60 );
		$reader=new Spreadsheet_Excel_Reader();
		$reader->setUTFEncoder('iconv');
		$reader->setOutputEncoding('UTF-8');
		$reader->read($filename);

		$this->clearCache();

		$ok = $this->uploadProducts( $reader, $database );
		if (!$ok) {
			return FALSE;
		}

		return $ok;
	}
	
	function edit( $filename ) {
		global $config;
		global $log;
		//$config = $this->config;
		//$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		$database =& $this->db;
		require_once 'pear/Spreadsheet/Excel/Reader.php';
		ini_set("memory_limit","512M");
		ini_set("max_execution_time",180);
		//set_time_limit( 60 );
		$reader=new Spreadsheet_Excel_Reader();
		$reader->setUTFEncoder('iconv');
		$reader->setOutputEncoding('UTF-8');
		$reader->read($filename);
		/*$ok = $this->validateUpload( $reader );
		if (!$ok) {
			return FALSE;
		}*/
		$this->clearCache();
		/*$ok = $this->uploadImages( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadCategories( $reader, $database );
		if (!$ok) {
			return FALSE;
		}*/
		$ok = $this->editProducts( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		/*$ok = $this->uploadOptions( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadSpecials( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadDiscounts( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadGlobalOptions( $reader, $database );
		if (!$ok) {
			return FALSE;
		}*/
		return $ok;
	}
	
	function updata( $filename ) {
		global $config;
		global $log;
		//$config = $this->config;
		//$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		$database =& $this->db;
		require_once 'pear/Spreadsheet/Excel/Reader.php';
		ini_set("memory_limit","512M");
		ini_set("max_execution_time",180);
		//set_time_limit( 60 );
		$reader=new Spreadsheet_Excel_Reader();
		$reader->setUTFEncoder('iconv');
		$reader->setOutputEncoding('UTF-8');
		$reader->read($filename);
		/*$ok = $this->validateUpload( $reader );
		if (!$ok) {
			return FALSE;
		}*/
		$this->clearCache();
		/*$ok = $this->uploadImages( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadCategories( $reader, $database );
		if (!$ok) {
			return FALSE;
		}*/
		$ok = $this->updataProducts( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		/*$ok = $this->uploadOptions( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadSpecials( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadDiscounts( $reader, $database );
		if (!$ok) {
			return FALSE;
		}
		$ok = $this->uploadGlobalOptions( $reader, $database );
		if (!$ok) {
			return FALSE;
		}*/
		return $ok;
	}




	function getStoreIdsForCategories( &$database ) {
		$sql =  "SELECT category_id, store_id FROM `category_to_store` cs;";
		$storeIds = array();
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$categoryId = $row['category_id'];
			$storeId = $row['store_id'];
			if (!isset($storeIds[$categoryId])) {
				$storeIds[$categoryId] = array();
			}
			if (!in_array($storeId,$storeIds[$categoryId])) {
				$storeIds[$categoryId][] = $storeId;
			}
		}
		return $storeIds;
	}


	function populateCategoriesWorksheet( &$worksheet, &$database, $languageId, &$boxFormat, &$textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->setColumn($j,$j++,strlen('category_id')+1);
		$worksheet->setColumn($j,$j++,strlen('parent_id')+1);
		$worksheet->setColumn($j,$j++,max(strlen('name'),32)+1);
		$worksheet->setColumn($j,$j++,strlen('sort_order')+1);
		$worksheet->setColumn($j,$j++,max(strlen('image_name'),12)+1);
		$worksheet->setColumn($j,$j++,max(strlen('date_added'),19)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('date_modified'),19)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('language_id'),2)+1);
		$worksheet->setColumn($j,$j++,max(strlen('seo_keyword'),16)+1);
		$worksheet->setColumn($j,$j++,max(strlen('description'),32)+1);
		$worksheet->setColumn($j,$j++,max(strlen('meta_description'),32)+1);
		$worksheet->setColumn($j,$j++,max(strlen('meta_keywords'),32)+1);
		$worksheet->setColumn($j,$j++,max(strlen('store_ids'),16)+1);
		$worksheet->setColumn($j,$j++,max(strlen('status'),5)+1,$textFormat);
		
		// The heading row
		$i = 0;
		$j = 0;
		$worksheet->writeString( $i, $j++, 'category_id', $boxFormat );
		$worksheet->writeString( $i, $j++, 'parent_id', $boxFormat );
		$worksheet->writeString( $i, $j++, 'name', $boxFormat );
		$worksheet->writeString( $i, $j++, 'sort_order', $boxFormat );
		$worksheet->writeString( $i, $j++, 'image_name', $boxFormat );
		$worksheet->writeString( $i, $j++, 'date_added', $boxFormat );
		$worksheet->writeString( $i, $j++, 'date_modified', $boxFormat );
		$worksheet->writeString( $i, $j++, 'language_id', $boxFormat );
		$worksheet->writeString( $i, $j++, 'seo_keyword', $boxFormat );
		$worksheet->writeString( $i, $j++, 'description', $boxFormat );
		$worksheet->writeString( $i, $j++, 'meta_description', $boxFormat );
		$worksheet->writeString( $i, $j++, 'meta_keywords', $boxFormat );
		$worksheet->writeString( $i, $j++, 'store_ids', $boxFormat );
		$worksheet->writeString( $i, $j++, "status\nenabled", $boxFormat );
		$worksheet->setRow( $i, 30, $boxFormat );
		
		// The actual categories data
		$i += 1;
		$j = 0;
		$storeIds = $this->getStoreIdsForCategories( $database );
		$query  = "SELECT c.* , cd.*, ua.keyword FROM `category` c ";
		$query .= "INNER JOIN `category_description` cd ON cd.category_id = c.category_id ";
		$query .= " AND cd.language_id=$languageId ";
		$query .= "LEFT JOIN `url_alias` ua ON ua.query=CONCAT('category_id=',c.category_id) ";
		$query .= "ORDER BY c.`parent_id`, `sort_order`, c.`category_id`;";
		$result = $database->query( $query );
		foreach ($result->rows as $row) {
			$worksheet->write( $i, $j++, $row['category_id'] );
			$worksheet->write( $i, $j++, $row['parent_id'] );
			$worksheet->writeString( $i, $j++, html_entity_decode($row['name'],ENT_QUOTES,'UTF-8') );
			$worksheet->write( $i, $j++, $row['sort_order'] );
			$worksheet->write( $i, $j++, $row['image'] );
			$worksheet->write( $i, $j++, $row['date_added'], $textFormat );
			$worksheet->write( $i, $j++, $row['date_modified'], $textFormat );
			$worksheet->write( $i, $j++, $row['language_id'] );
			$worksheet->writeString( $i, $j++, ($row['keyword']) ? $row['keyword'] : '' );
			$worksheet->writeString( $i, $j++, html_entity_decode($row['description']/*'aaa'*/,ENT_QUOTES,'UTF-8') );
			$worksheet->writeString( $i, $j++, html_entity_decode($row['meta_description'],ENT_QUOTES,'UTF-8') );
			$worksheet->writeString( $i, $j++, html_entity_decode($row['meta_keywords'],ENT_QUOTES,'UTF-8') );
			$storeIdList = '';
			$categoryId = $row['category_id'];
			if (isset($storeIds[$categoryId])) {
				foreach ($storeIds[$categoryId] as $storeId) {
					$storeIdList .= ($storeIdList=='') ? $storeId : ','.$storeId;
				}
			}
			$worksheet->write( $i, $j++, $storeIdList, $textFormat );
			$worksheet->write( $i, $j++, ($row['status']==0) ? "false" : "true", $textFormat );
			$i += 1;
			$j = 0;
			//break;
		}
	}


	function getStoreIdsForProducts( &$database ) {
		$sql =  "SELECT product_id, store_id FROM `product_to_store` ps;";
		$storeIds = array();
		$result = $database->query( $sql );
		foreach ($result->rows as $row) {
			$productId = $row['product_id'];
			$storeId = $row['store_id'];
			if (!isset($storeIds[$productId])) {
				$storeIds[$productId] = array();
			}
			if (!in_array($storeId,$storeIds[$productId])) {
				$storeIds[$productId][] = $storeId;
			}
		}
		return $storeIds;
	}


	function populateProductsWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$weightFormat, &$textFormat, &$order_ids )
	{
		// Set the column widths
		$j = 0;
		$worksheet->setColumn($j,$j++,max(strlen('product_name'),30)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('quantity'),2)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('price'),4)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('customer_name'),15)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('invoice_no'),15)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('payment_method'),53)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('status'),4)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('order_num'),2)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('total'),20)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('date_added'),17)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('shipping_company'),20)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('shipping_code'),20)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('remarks'),20)+1,$textFormat);

		// The product headings row
		$i = 0;
		$j = 0;
		
		$worksheet->writeString( $i, $j++, 'product_name', $boxFormat );
		$worksheet->writeString( $i, $j++, 'quantity', $boxFormat );
		$worksheet->writeString( $i, $j++, 'price', $boxFormat );
		$worksheet->writeString( $i, $j++, 'customer_name', $boxFormat );
		$worksheet->writeString( $i, $j++, 'invoice_no', $boxFormat );
		$worksheet->writeString( $i, $j++, 'payment_method', $boxFormat );
		$worksheet->writeString( $i, $j++, 'status', $boxFormat );
		$worksheet->writeString( $i, $j++, 'order_num', $boxFormat );
		$worksheet->writeString( $i, $j++, 'total', $boxFormat );
		$worksheet->writeString( $i, $j++, 'date_added', $boxFormat );
		$worksheet->writeString( $i, $j++, 'shipping_company', $boxFormat );
		$worksheet->writeString( $i, $j++, 'shipping_code', $boxFormat );
		$worksheet->writeString( $i, $j++, 'remarks', $boxFormat );
		$worksheet->setRow( $i, 30, $boxFormat );

		// The actual products data
		$i += 1;
		$j = 0;
		
		$str = explode("-",$order_ids);
		for($k=0;$k<count($str);$k++){
			$orders[$k] = $str[$k];
		}

		foreach ($orders as $orderid) {
			$query  = "SELECT ";
			$query .= " o.order_id,";
			$query .= " o.invoice_no,";
			$query .= " o.payment_method,";
			$query .= " o.remarks,";
			$query .= " o.company_id,";
			$query .= " o.shipping_code,";
			$query .= " o.shipping_country_id,";
			$query .= " o.date_added,";
			$query .= " o.total,";
			$query .= " ot.text,";
			$query .= " os.name,";
			$query .= " c.lastname ";
			$query .= "FROM `order` o ";
			$query .= "LEFT JOIN `order_status` os ON o.order_status_id=os.order_status_id ";
			$query .= "LEFT JOIN `order_total` ot ON o.order_id=ot.order_id ";
			$query .= "LEFT JOIN `customer` c ON c.customer_id=o.customer_id ";
			$query .= "WHERE o.order_id='" . (int)$orderid . "'";
			$query .= "AND ot.sort_order='3'";
			$query .= "GROUP BY o.order_id ";
			$result =  $this->db->query( $query );
			foreach ($result->result_array() as $row) {
				$order_id= $row['order_id'];
				$products_result = $this->db->query("SELECT name, quantity, price FROM `order_product` WHERE order_id = '" . (int)$order_id . "'");
				$product='';
				$ki=0;
				foreach($products_result->result_array() as $product_result){
					$keyi = $i+$ki;
					$worksheet->writeString( $keyi, $j++, html_entity_decode($product_result['name'],ENT_QUOTES,'UTF-8') );
					$worksheet->write( $keyi, $j++, $product_result['quantity'] );
					$worksheet->write( $keyi, $j++, $product_result['price'] );
					$ki++;
					$j = 0;
				}
				$j=3;
				$worksheet->writeString( $i, $j++, html_entity_decode($row['lastname'],ENT_QUOTES,'UTF-8') );
				$worksheet->write( $i, $j++, date("Ymd",strtotime($row['date_added'])) . sprintf( "%07d ",$row['invoice_no']) );
				$worksheet->writeString( $i, $j++, html_entity_decode($row['payment_method'],ENT_QUOTES,'UTF-8') );
				$worksheet->writeString( $i, $j++, html_entity_decode($row['name'],ENT_QUOTES,'UTF-8') );
				$worksheet->write( $i, $j++, $row['order_id'] );
				$worksheet->write( $i, $j++, $row['total'].html_entity_decode('(含運費:'.$row['text'].')',ENT_QUOTES,'UTF-8') );
				$worksheet->write( $i, $j++, $row['date_added'] );
				
				if($row['company_id']==0){
					if($row['shipping_country_id']==96||$row['shipping_country_id']==44||$row['shipping_country_id']==125||$row['shipping_country_id']==206){
						$company_id = 1;
					}else{
						$company_id = 2;
					}
				}else{
					$company_id = $row['company_id'];
				}
				
				$query_company = "SELECT name FROM shipping_company WHERE company_id = '" . (int)$company_id . "'";
				$result_company =  $this->db->query( $query_company );
				foreach ($result_company->result_array() as $row_company) {
					$worksheet->writeString( $i, $j++, html_entity_decode($row_company['name'],ENT_QUOTES,'UTF-8') );
				}
				
				$worksheet->writeString( $i, $j++, html_entity_decode($row['shipping_code'],ENT_QUOTES,'UTF-8') );
				$worksheet->writeString( $i, $j++, html_entity_decode($row['remarks'],ENT_QUOTES,'UTF-8') );
				
				$i = $keyi;
				$i += 1;
				$j = 0;
		
			}
		}
	}


	function populateOptionsWorksheet( &$worksheet, &$database, $languageId, &$priceFormat, &$boxFormat, $textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->setColumn($j,$j++,max(strlen('product_id'),4)+1);
		$worksheet->setColumn($j,$j++,max(strlen('language_id'),2)+1);
		$worksheet->setColumn($j,$j++,max(strlen('option'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('option_value'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('quantity'),4)+1);
		$worksheet->setColumn($j,$j++,max(strlen('subtract'),5)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('price'),10)+1,$priceFormat);
		$worksheet->setColumn($j,$j++,max(strlen('prefix'),5)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('sort_order'),5)+1);
		
		// The options headings row
		$i = 0;
		$j = 0;
		$worksheet->writeString( $i, $j++, 'product_id', $boxFormat );
		$worksheet->writeString( $i, $j++, 'language_id', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'option', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'option_value', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'quantity', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'subtract', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'price', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'prefix', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'sort_order', $boxFormat  );
		$worksheet->setRow( $i, 30, $boxFormat );
		
		// The actual options data
		$i += 1;
		$j = 0;
		$query  = "SELECT DISTINCT p.product_id, ";
		$query .= "  pod.name AS option_name, ";
		$query .= "  po.sort_order AS option_sort_order, ";
		$query .= "  povd.name AS option_value, ";
		$query .= "  pov.quantity AS option_quantity, ";
		$query .= "  pov.subtract AS option_subtract, ";
		$query .= "  pov.price AS option_price, ";
		$query .= "  pov.prefix AS option_prefix, ";
		$query .= "  pov.sort_order AS sort_order ";
		$query .= "FROM `product` p ";
		$query .= "INNER JOIN `product_description` pd ON p.product_id=pd.product_id ";
		$query .= "  AND pd.language_id=$languageId ";
		$query .= "INNER JOIN `product_option` po ON po.product_id=p.product_id ";
		
		$query .= "INNER JOIN `product_option_description` pod ON pod.product_option_id=po.product_option_id ";
		$query .= "  AND pod.product_id=po.product_id ";
		$query .= "  AND pod.language_id=$languageId ";
		$query .= "INNER JOIN `product_option_value` pov ON pov.product_option_id=po.product_option_id ";
		
		$query .= "INNER JOIN `product_option_value_description` povd ON povd.product_option_value_id=pov.product_option_value_id ";
		$query .= "  AND povd.language_id=$languageId ";
		$query .= "ORDER BY product_id, option_sort_order, sort_order;";
		$result = $database->query( $query );
		foreach ($result->rows as $row) {
			$worksheet->write( $i, $j++, $row['product_id'] );
			$worksheet->write( $i, $j++, $languageId );
			$worksheet->writeString( $i, $j++, $row['option_name'] );
			$worksheet->writeString( $i, $j++, $row['option_value'] );
			$worksheet->write( $i, $j++, $row['option_quantity'] );
			$worksheet->write( $i, $j++, ($row['option_subtract']==0) ? "false" : "true", $textFormat );
			$worksheet->write( $i, $j++, $row['option_price'], $priceFormat );
			$worksheet->writeString( $i, $j++, $row['option_prefix'], $textFormat );
			$worksheet->write( $i, $j++, $row['sort_order'] );
			$i += 1;
			$j = 0;
		}
	}


	function populateSpecialsWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->setColumn($j,$j++,strlen('product_id')+1);
		$worksheet->setColumn($j,$j++,strlen('customer_group')+1);
		$worksheet->setColumn($j,$j++,strlen('priority')+1);
		$worksheet->setColumn($j,$j++,max(strlen('price'),10)+1,$priceFormat);
		$worksheet->setColumn($j,$j++,max(strlen('date_start'),19)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('date_end'),19)+1,$textFormat);
		
		// The heading row
		$i = 0;
		$j = 0;
		$worksheet->writeString( $i, $j++, 'product_id', $boxFormat );
		$worksheet->writeString( $i, $j++, 'customer_group', $boxFormat );
		$worksheet->writeString( $i, $j++, 'priority', $boxFormat );
		$worksheet->writeString( $i, $j++, 'price', $boxFormat );
		$worksheet->writeString( $i, $j++, 'date_start', $boxFormat );
		$worksheet->writeString( $i, $j++, 'date_end', $boxFormat );
		$worksheet->setRow( $i, 30, $boxFormat );
		
		// The actual product specials data
		$i += 1;
		$j = 0;
		$query  = "SELECT ps.*, cg.name FROM `product_special` ps ";
		$query .= "LEFT JOIN `customer_group` cg ON cg.customer_group_id=ps.customer_group_id ";
		$query .= "ORDER BY ps.product_id, cg.name";
		$result = $database->query( $query );
		foreach ($result->rows as $row) {
			$worksheet->write( $i, $j++, $row['product_id'] );
			$worksheet->write( $i, $j++, $row['name'] );
			$worksheet->write( $i, $j++, $row['priority'] );
			$worksheet->write( $i, $j++, $row['price'], $priceFormat );
			$worksheet->write( $i, $j++, $row['date_start'], $textFormat );
			$worksheet->write( $i, $j++, $row['date_end'], $textFormat );
			$i += 1;
			$j = 0;
		}
	}


	function populateDiscountsWorksheet( &$worksheet, &$database, &$priceFormat, &$boxFormat, &$textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->setColumn($j,$j++,strlen('product_id')+1);
		$worksheet->setColumn($j,$j++,strlen('customer_group')+1);
		$worksheet->setColumn($j,$j++,strlen('quantity')+1);
		$worksheet->setColumn($j,$j++,strlen('priority')+1);
		$worksheet->setColumn($j,$j++,max(strlen('price'),10)+1,$priceFormat);
		$worksheet->setColumn($j,$j++,max(strlen('date_start'),19)+1,$textFormat);
		$worksheet->setColumn($j,$j++,max(strlen('date_end'),19)+1,$textFormat);
		
		// The heading row
		$i = 0;
		$j = 0;
		$worksheet->writeString( $i, $j++, 'product_id', $boxFormat );
		$worksheet->writeString( $i, $j++, 'customer_group', $boxFormat );
		$worksheet->writeString( $i, $j++, 'quantity', $boxFormat );
		$worksheet->writeString( $i, $j++, 'priority', $boxFormat );
		$worksheet->writeString( $i, $j++, 'price', $boxFormat );
		$worksheet->writeString( $i, $j++, 'date_start', $boxFormat );
		$worksheet->writeString( $i, $j++, 'date_end', $boxFormat );
		$worksheet->setRow( $i, 30, $boxFormat );
		
		// The actual product discounts data
		$i += 1;
		$j = 0;
		$query  = "SELECT pd.*, cg.name FROM `product_discount` pd ";
		$query .= "LEFT JOIN `customer_group` cg ON cg.customer_group_id=pd.customer_group_id ";
		$query .= "ORDER BY pd.product_id, cg.name";
		$result = $database->query( $query );
		foreach ($result->rows as $row) {
			$worksheet->write( $i, $j++, $row['product_id'] );
			$worksheet->write( $i, $j++, $row['name'] );
			$worksheet->write( $i, $j++, $row['quantity'] );
			$worksheet->write( $i, $j++, $row['priority'] );
			$worksheet->write( $i, $j++, $row['price'], $priceFormat );
			$worksheet->write( $i, $j++, $row['date_start'], $textFormat );
			$worksheet->write( $i, $j++, $row['date_end'], $textFormat );
			$i += 1;
			$j = 0;
		}
	}


	function populateGlobalOptionsWorksheet( &$worksheet, &$database, $languageId, &$priceFormat, &$boxFormat, $textFormat )
	{
		// Set the column widths
		$j = 0;
		$worksheet->setColumn($j,$j++,max(strlen('name'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('sku'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('product_id'),4)+1);
		$worksheet->setColumn($j,$j++,max(strlen('language_id'),2)+1);
		$worksheet->setColumn($j,$j++,max(strlen('global_option_id'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('global_option_value_id'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('global_option'),30)+1);
		$worksheet->setColumn($j,$j++,max(strlen('global_option_value'),30)+1);
		//$worksheet->setColumn($j,$j++,max(strlen('sort_order'),2)+1);
		
		// The options headings row
		$i = 0;
		$j = 0;
		$worksheet->writeString( $i, $j++, 'name', $boxFormat );
		$worksheet->writeString( $i, $j++, 'sku', $boxFormat );
		$worksheet->writeString( $i, $j++, 'product_id', $boxFormat );
		$worksheet->writeString( $i, $j++, 'language_id', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'global_option_id', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'global_option_value_id', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'global_option', $boxFormat  );
		$worksheet->writeString( $i, $j++, 'global_option_value', $boxFormat  );
		//$worksheet->writeString( $i, $j++, 'sort_order', $boxFormat  );
		$worksheet->setRow( $i, 30, $boxFormat );
		
		// The actual options data
		$i += 1;
		$j = 0;
		$query  = "SELECT DISTINCT p.product_id, p.sku, pd.name, ";
		$query .= "  po.global_option_id AS global_option_id, ";
		$query .= "  gpo.value_type AS value_type, ";
		$query .= "  gpo.sort_order AS sort_order, ";
		$query .= "  pov.global_option_value_id AS global_option_value_id, ";
		$query .= "  gpod.name AS global_option_name, ";
		$query .= "  gpovd.name AS global_option_value_name, ";
		$query .= "  povd.name AS option_value_name ";
		
		$query .= "FROM `product` p ";
		$query .= "INNER JOIN `product_description` pd ON pd.product_id=p.product_id ";
		$query .= "INNER JOIN `product_option` po ON po.product_id=p.product_id ";
		$query .= "INNER JOIN `global_product_option` gpo ON gpo.global_option_id=po.global_option_id ";
		$query .= "INNER JOIN `global_product_option_description` gpod ON gpod.global_option_id=po.global_option_id ";
		$query .= "  AND gpod.language_id=$languageId ";
		$query .= "INNER JOIN `product_option_value` pov ON pov.product_option_id=po.product_option_id ";
		$query .= "LEFT JOIN `global_product_option_value_description` gpovd ON gpovd.global_option_value_id=pov.global_option_value_id ";
		$query .= "  AND pov.global_option_value_id !=0 ";
		$query .= "  AND gpovd.language_id=$languageId ";
		$query .= "LEFT JOIN `product_option_value_description` povd ON povd.product_option_value_id=pov.product_option_value_id ";
		$query .= "  AND pov.global_option_value_id=0 ";
		$query .= "  AND povd.language_id=$languageId ";/**/
		$query .= "  WHERE po.sort_order = 0 AND po.global_option_id > 0 ";
		$query .= "ORDER BY  product_id ASC, sort_order ASC, gpod.name ASC; ";
		$result = $database->query( $query );
		foreach ($result->rows as $row) {
			$worksheet->write( $i, $j++, $row['name'] );
			$worksheet->write( $i, $j++, $row['sku'] );
			$worksheet->write( $i, $j++, $row['product_id'] );
			$worksheet->write( $i, $j++, $languageId );
			$worksheet->writeString( $i, $j++, $row['global_option_id'] );
			if($row['global_option_value_id'] != 0)
			{
				$worksheet->writeString( $i, $j++, $row['global_option_value_id'] );
			}
			elseif($row['value_type'] == 3)
			{
				$worksheet->writeString( $i, $j++, 0/*'text'*/ );
			}
			$worksheet->writeString( $i, $j++, $row['global_option_name'] );
			if($row['global_option_value_id'] != 0)
			{
				$worksheet->writeString( $i, $j++, $row['global_option_value_name'] );
			}
			elseif($row['value_type'] == 3)
			{
				$worksheet->writeString( $i, $j++, $row['option_value_name']/*'text'*/ );
			}
			//$worksheet->write( $i, $j++, $row['sort_order'] );
			
			$i += 1;
			$j = 0;
		}
	}


	function download1($order_ids) {
		global $config;
		global $log;
		//$config = $this->config;
		//$log = $this->log;
		set_error_handler('error_handler_for_export',E_ALL);
		$database =& $this->db;
		//$languageId = $this->getDefaultLanguageId($database);

		// We use the package from http://pear.php.net/package/Spreadsheet_Excel_Writer/
		require_once "pear/Spreadsheet/Excel/Writer.php";
		
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$workbook->setTempDir(DIR_CACHE);
		$workbook->setVersion(8); // Use Excel97/2000 Format
		$priceFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '######0.00'));
		$boxFormat =& $workbook->addFormat(array('Size' => 10,'vAlign' => 'vequal_space' ));
		$weightFormat =& $workbook->addFormat(array('Size' => 10,'Align' => 'right','NumFormat' => '##0.00'));
		$textFormat =& $workbook->addFormat(array('Size' => 10, 'NumFormat' => "@" ));
		
		// sending HTTP headers
		$workbook->send('order_information.xls');
		
		// Get all additional product images
		$imageNames = array();
		
		// Creating the products worksheet
		
		
		$worksheet =& $workbook->addWorksheet('Order Information');
		$worksheet->setInputEncoding ( 'UTF-8' );
		$this->populateProductsWorksheet( $worksheet, $database, $priceFormat, $boxFormat, $weightFormat, $textFormat, $order_ids );
		$worksheet->freezePanes(array(1, 1, 1, 1));
		
		// Let's send the file
		$workbook->close();
		exit;
	}


}
?>